package com.jhgsys.internal.common.utils;

import com.google.common.collect.Lists;
import com.wuwenze.poi.annotation.ExcelField;
import com.wuwenze.poi.config.Options;
import com.wuwenze.poi.convert.ReadConverter;
import com.wuwenze.poi.convert.WriteConverter;
import com.wuwenze.poi.exception.ExcelKitAnnotationAnalyzeException;
import com.wuwenze.poi.exception.ExcelKitRuntimeException;
import com.wuwenze.poi.pojo.ExcelMapping;
import com.wuwenze.poi.pojo.ExcelProperty;
import com.wuwenze.poi.util.Const;
import com.wuwenze.poi.util.POIUtil;
import com.wuwenze.poi.util.ValidatorUtil;
import com.wuwenze.poi.validator.Validator;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URL;
import java.net.URLEncoder;
import java.text.ParseException;
import java.util.*;

/**
 * 支持导入xls，xlsx的文件
 * 支持生成带格式的Excel，顶部标题栏单元格合并，列名蓝色背景，文本居左，数字居右，日期居中
 * @author chenweihua
 * @date 2019-07-23
 */
@Slf4j
public class ExcelUtils {

    /**
     * 每个sheet的最大行数
     */
    private static final int mMaxSheetRecords = 50000;

    /**
     * 居右样式
     */
    private static CellStyle mRightCellStyle = null;
    /**
     * 居中样式
     */
    private static CellStyle mCenterCellStyle = null;
    /**
     * 列名样式
     */
    private static CellStyle mHeaderCellStyle = null;
    /**
     * 标题栏样式
     */
    private static CellStyle mPageHeaderCellStyle = null;

    /**
     * 导入Excel文件
     *
     * @param filename      导入文件名
     * @param inputStream   输入流
     * @param dataBegin     数据开始行，从0开始
     * @param excelDataWarp 数据转换器
     */
    public static void readExcel(String filename, InputStream inputStream, int dataBegin, ExcelDataWarp excelDataWarp) {
        readExcel(filename, inputStream, dataBegin, 0, excelDataWarp);
    }

    /**
     * 导入Excel文件 默认第一个sheet
     *
     * @param filename      导入文件名
     * @param inputStream   输入流
     * @param dataBegin     数据开始行，从0开始
     * @param dataBegin     数据列数量（个数）
     * @param excelDataWarp 数据转换器
     */
    public static void readExcel(String filename, InputStream inputStream, int dataBegin, int columns, ExcelDataWarp excelDataWarp) {
        if (StringUtils.endsWith(filename, ".xls")) {
            readXls(inputStream, dataBegin, columns, 0, excelDataWarp);
        } else if (StringUtils.endsWith(filename, ".xlsx")) {
            readXlsx(inputStream, dataBegin, columns, 0, excelDataWarp);
        }
    }

    /**
     * 导入Excel 指定sheet
     *
     * @param filename
     * @param inputStream
     * @param dataBegin
     * @param columns
     * @param excelDataWarp
     */
    public static void readExcel(String filename, InputStream inputStream, int dataBegin, int columns, int sheet, ExcelDataWarp excelDataWarp) {
        if (StringUtils.endsWith(filename, ".xls")) {
            readXls(inputStream, dataBegin, columns, sheet, excelDataWarp);
        } else if (StringUtils.endsWith(filename, ".xlsx")) {
            readXlsx(inputStream, dataBegin, columns, sheet, excelDataWarp);
        }
    }

    /**
     * 导入xls文件
     *
     * @param inputStream   输入流
     * @param dataBegin     数据开始行
     * @param excelDataWarp 数据转换器
     */
    public static void readXls(InputStream inputStream, int dataBegin, int columns, Integer sheetIndex, ExcelDataWarp excelDataWarp) {
//        int sheetIndex = 0;
        try {
            try {
                //通过poi的方式进行读取
                POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
                //声明工作簿
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);
                //进入sheet页
                HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(sheetIndex);
                //读取Excel数据
                int beginRow = hssfSheet.getFirstRowNum();
                int endRow = hssfSheet.getPhysicalNumberOfRows();
                int begin = Math.max(beginRow, dataBegin);
                //读取excel数据
                for (int i = begin; i < endRow; i++) {
                    try {
                        //获取某一行的数据
                        HSSFRow excelRow = hssfSheet.getRow(i);
                        if (excelRow == null) {
                            continue;
                        }
                        List line = new ArrayList();
                        int lastCellNum = Math.max(columns, excelRow.getLastCellNum());
                        for (int j = 0; j < lastCellNum; j++) {
                            HSSFCell rowCell = excelRow.getCell(j);
                            if (rowCell != null) {
                                line.add(getCellFormatValue(rowCell));
                            }

                        }
                        excelDataWarp.warpData(i,line);
                    } catch (Exception e) {
                        log.error("Import Excel Error:" + e.getMessage(), e);
                        excelDataWarp.onError(sheetIndex, i, e.getMessage());
                    }
                }
            } finally {
                inputStream.close();
            }
        } catch (IOException e) {
            log.error("Import Excel Error:" + e.getMessage(), e);
            excelDataWarp.onError(sheetIndex, -1, e.getMessage());
        }
    }

    public static Object readExcelFirstCell(String filename, InputStream inputStream, Integer sheetNum) throws Exception {
        if (StringUtils.endsWith(filename, ".xls")) {
            return readXlsCell(inputStream, 1, 1, sheetNum);
        } else if (StringUtils.endsWith(filename, ".xlsx")) {
            return readXlsxCell(inputStream, 1, 1, sheetNum);
        }
        return "";
    }

    public static boolean readExcelRowCell(String filename, InputStream inputStream, Integer sheetNum, int row, int cell) throws Exception {
        if (StringUtils.endsWith(filename, ".xls")) {
            return readXlsRowCell(inputStream, sheetNum, row, cell);
        } else if (StringUtils.endsWith(filename, ".xlsx")) {
            return readXlsxRowCell(inputStream, sheetNum, row, cell);
        } else {
            return false;
        }
    }

    private static boolean readXlsRowCell(InputStream inputStream, Integer sheetIndex, int row, int cell) throws Exception {
        int h = row - 1, l = cell - 1;
        try {
            //通过poi的方式进行读取
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
            //声明工作簿
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);
            //进入sheet页
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(sheetIndex);
            //读取Excel数据
            int endRow = hssfSheet.getPhysicalNumberOfRows();
            if (endRow < h) {
                return false;
            }
            HSSFRow excelRow = hssfSheet.getRow(h);
            int endColumn = excelRow.getLastCellNum();
            return endColumn == l;
        } finally {
            inputStream.close();
        }
    }

    private static boolean readXlsxRowCell(InputStream inputStream, Integer sheetIndex, int row, int cell) throws Exception {
        int h = row, l = cell;
        try {
            //通过poi的方式进行读取
            //声明工作簿
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            //进入sheet页
            XSSFSheet hssfSheet = workbook.getSheetAt(sheetIndex);
            //进入sheet页
            //读取Excel数据
            int endRow = hssfSheet.getPhysicalNumberOfRows();
            if (endRow < h) {
                return false;
            }
            XSSFRow excelRow = hssfSheet.getRow(h);
            int endColumn = excelRow.getLastCellNum();
            return endColumn == l;
        } finally {
            inputStream.close();
        }
    }

    public static Integer sheetContain(String filename, InputStream inputStream, String type) throws Exception {
        if (StringUtils.endsWith(filename, ".xls")) {
            return readXlsSheet(inputStream, type);
        } else if (StringUtils.endsWith(filename, ".xlsx")) {
            return readXlsxSheet(inputStream, type);
        }
        return -1;
    }

    private static Integer readXlsxSheet(InputStream inputStream, String type) throws Exception {
        Integer result = -1;
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            int sheetNum = workbook.getNumberOfSheets();
            if (sheetNum == 1) {
                return 0;
            }
            for (int i = 0; i < sheetNum; i++) {
                XSSFSheet hssfSheet = workbook.getSheetAt(i);
                if (type.equals(hssfSheet.getSheetName().replaceAll("\\s*", ""))) {
                    return i;
                }
            }
        } finally {
            inputStream.close();
        }
        return result;
    }

    private static Integer readXlsSheet(InputStream inputStream, String type) throws Exception {
        Integer result = -1;
        try {
            //通过poi的方式进行读取
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
            //声明工作簿
            HSSFWorkbook workbook = new HSSFWorkbook(poifsFileSystem);
            int sheetNum = workbook.getNumberOfSheets();
            if (sheetNum == 1) {//只有一个sheet 返回sheet的index
                return 0;
            }
            for (int i = 0; i < sheetNum; i++) {
                HSSFSheet hssfSheet = workbook.getSheetAt(i);
                if (type.equals(hssfSheet.getSheetName())) {
                    return i;
                }
            }
        } finally {
            inputStream.close();
        }
        return result;
    }


    private static Object readXlsxCell(InputStream inputStream, int hp, int lp, Integer sheetIndex) throws Exception {
        int h = hp - 1, l = lp - 1;
//        int sheetIndex = 0;
        try {
            //声明工作簿
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            //进入sheet页
            XSSFSheet hssfSheet = workbook.getSheetAt(sheetIndex);
            //读取Excel数据
            int beginRow = hssfSheet.getFirstRowNum();
            int endRow = hssfSheet.getPhysicalNumberOfRows();
            if (h > endRow || h < beginRow) {
                throw new Exception("行号在Excel文件中不存在！");
            }
            XSSFRow excelRow = hssfSheet.getRow(h);
            int endColumn = excelRow.getLastCellNum();
            if (l > endColumn) {
                throw new Exception("列号在Excel文件中不存在！");
            }
            XSSFCell rowCell = excelRow.getCell(l);
            if (rowCell == null) {
                return null;
            } else {
                return getCellFormatValue(rowCell);
            }
        } finally {
            inputStream.close();
        }
    }

    private static Object readXlsCell(InputStream inputStream, int hp, int lp, Integer sheetIndex) throws Exception {
        int h = hp - 1, l = lp - 1;
        try {
            //通过poi的方式进行读取
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
            //声明工作簿
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);
            //进入sheet页
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(sheetIndex);
            //读取Excel数据
            int beginRow = hssfSheet.getFirstRowNum();
            int endRow = hssfSheet.getPhysicalNumberOfRows();
            if (h > endRow || h < beginRow) {
                throw new Exception("行号在Excel文件中不存在！");
            }
            HSSFRow excelRow = hssfSheet.getRow(h);
            int endColumn = excelRow.getLastCellNum();
            if (l > endColumn) {
                throw new Exception("列号在Excel文件中不存在！");
            }
            HSSFCell rowCell = excelRow.getCell(l);
            if (rowCell == null) {
                return null;
            } else {
                return getCellFormatValue(rowCell);
            }
        } finally {
            inputStream.close();
        }
    }

    /**
     * 导入xlsx文件
     *
     * @param inputStream   输入流
     * @param dataBegin     数据开始行
     * @param excelDataWarp 数据转换器
     */
    public static void readXlsx(InputStream inputStream, int dataBegin, int columns, Integer sheetIndex, ExcelDataWarp excelDataWarp) {
//        int sheetIndex = 0;
        try {
            try {
                //声明工作簿
                XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
                //进入sheet页
                XSSFSheet hssfSheet = workbook.getSheetAt(sheetIndex);
                //读取Excel数据
                Row row = null;
                //sheetIndex =  dataBegin
                //startRadLine = columns
                //tailLine = sheetIndex
//                String[] res = new String[hssfSheet.getLastRowNum() - sheetIndex + 1];
                int beginRow = hssfSheet.getFirstRowNum();
                int endRow = hssfSheet.getPhysicalNumberOfRows();
                int begin = Math.max(beginRow, dataBegin);
                //读取excel数据
                for (int i = begin; i < endRow; i++) {
                    try {
                        //获取某一行的数据
                        XSSFRow excelRow = hssfSheet.getRow(i);
                        if (excelRow == null) {
                            continue;
                        }
                        List line = new ArrayList();
                        int lastCellNum = Math.max(columns, excelRow.getLastCellNum());
                        for (int j = 0; j < lastCellNum; j++) {
                            XSSFCell rowCell = excelRow.getCell(j);
                            if (rowCell != null) {
                                line.add(getCellFormatValue(rowCell));
                            }
                        }
                        excelDataWarp.warpData(i, line);
                    } catch (Exception e) {
                        log.error("Import Excel Error:" + e.getMessage(), e);
                        excelDataWarp.onError(sheetIndex, i, e.getMessage());
                    }
                }
            } finally {
                inputStream.close();
            }
        } catch (Exception e) {
            log.error("Import Excel Error:" + e.getMessage(), e);
            excelDataWarp.onError(sheetIndex, -1, e.getMessage());
        }
    }

    /**
     * 格式化读取单元格值
     *
     * @param cell
     * @return
     */
    public static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        try {
            if (cell != null) {
                // 判断cell类型
                switch (cell.getCellType()) {
                    case NUMERIC:
                    case FORMULA:
                        // 判断cell是否为日期格式
                        if (DateUtil.isCellDateFormatted(cell)) {
                            // 转换为日期格式YYYY-mm-dd
                            cellValue = DateUtil.getJavaDate(cell.getNumericCellValue());
                        } else {
                            // 数字
                            if (cell.getNumericCellValue() == (int) cell.getNumericCellValue()) {
                                cellValue = (int) cell.getNumericCellValue();
                            } else {
                                cellValue = cell.getNumericCellValue();
                            }
                        }
                        break;
                    case STRING:
                        cellValue = cell.getRichStringCellValue().getString();
                        break;
                    default:
                        cellValue = null;
                }
            }
        } catch (Exception e) {
            cellValue = e.getMessage();
        }
        return cellValue;
    }

    /**
     * 导出Excel文件（带模板）
     *
     * @param response 输出流
     * @param template 模板文件
     * @param offRow   数据开始行，从0开始
     * @param datas    数据
     */
    public static void downExcel(HttpServletResponse response, String template, int offRow, List<Object[]> datas) {
        try {
            URL templateUrl = ExcelUtils.class.getResource("/excel/templates/");
            String templatePath = "";
            if (templateUrl != null) {
                templatePath = templateUrl.toURI().getPath();
            }
            if (StringUtils.isEmpty(templatePath)) {
                templatePath = System.getProperties().getProperty("java.io.tmpdir");
                File file = new File(templatePath , template);
                FileUtils.copyInputStreamToFile(Objects.requireNonNull(ExcelUtils.class.getClassLoader().getResourceAsStream("classpath:excel/templates/" + template)), file);
            }
            String path = templatePath;
            log.info("模板文件路径：{}", path);
            File file = new File(path, template);
            FileInputStream in = new FileInputStream(file);
            try {
                XSSFWorkbook workbook = new XSSFWorkbook(in);
                generateXlsxWorkbook(workbook, "", null, offRow, datas);
                String fileName = System.currentTimeMillis() + ".xlsx";
                download(workbook, response, URLEncoder.encode(fileName, "UTF-8"));
            } finally {
                in.close();
            }
        } catch (Exception t) {
            throw new ExcelKitRuntimeException("downXlsx error", t);
        }
    }

    /**
     * 生成EXCEL
     *
     * @param pageTitle 标题名称
     * @param titles    列名称
     * @param datas     数据
     * @return
     */
    public static XSSFWorkbook generateXlsxWorkbook(XSSFWorkbook workbook, String pageTitle, List<String> titles, int offRow, List<Object[]> datas) {
        double sheetNo = Math.ceil((double) datas.size() / (double) mMaxSheetRecords);

        for (int index = 0; (double) index <= (sheetNo == 0.0D ? sheetNo : sheetNo - 1.0D); ++index) {
            XSSFSheet sheet = workbook.getSheetAt(index);
            if (sheet == null) {
                String sheetName = "sheet_" + (index + 1);//sheet名
                sheet = workbook.createSheet(sheetName);
            }

            if (null != datas && datas.size() > 0) {
                int startNo = index * mMaxSheetRecords;
                int endNo = Math.min(startNo + mMaxSheetRecords, datas.size());
                int columns = datas.get(0).length;
                if (titles != null) {
                    columns = titles.size();
                }
                for (int i = startNo; i < endNo; ++i) {
                    int rowIndex = i + offRow - startNo;
                    //创建行
                    XSSFRow bodyRow = sheet.getRow(rowIndex);
                    if (bodyRow == null) {
                        bodyRow = sheet.createRow(rowIndex);
                    }
                    for (int j = 0; j < columns; ++j) {
                        //创建单元格
                        XSSFCell cell = bodyRow.getCell(j);
                        if (cell == null) {
                            cell = bodyRow.createCell(j);
                        }
                        buildCellValueByExcelProperty(workbook, cell, datas.get(i)[j]);
                    }
                }

            }
        }
        return workbook;
    }

    public static void download(XSSFWorkbook wb, HttpServletResponse response, String filename) {
        try {
            OutputStream out = response.getOutputStream();
            response.setContentType(Const.XLSX_CONTENT_TYPE);
            response.setHeader(Const.XLSX_HEADER_KEY,
                    String.format(Const.XLSX_HEADER_VALUE_TEMPLATE, filename));
            write(wb, out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void write(XSSFWorkbook wb, OutputStream out) {
        try {
            if (null != out) {
                wb.write(out);
                out.flush();
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != out) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 导出Excel
     *
     * @param response  输出流
     * @param pageTitle 标题名称
     * @param titles    列名称
     * @param datas     数据
     */
    public static void downXlsx(HttpServletResponse response, String pageTitle, String[] titles, List<Object[]> datas) {
        try {
            SXSSFWorkbook workbook = POIUtil.newSXSSFWorkbook();
            try {
                SXSSFSheet sheet = workbook.createSheet();
                //ExcelMapping excelMapping = ExcelMappingFactory.get(clazz);
                List<String> listTitle = Arrays.asList(titles);
                generateXlsxWorkbook(workbook, pageTitle, listTitle, 2, datas, sheet);
                String fileName = System.currentTimeMillis() + ".xlsx";
                POIUtil.download(workbook, response, URLEncoder.encode(fileName, "UTF-8"));
            } finally {
                workbook.close();
            }
        } catch (Exception t) {
            throw new ExcelKitRuntimeException("downXlsx error", t);
        }
    }

    private static SXSSFWorkbook generateXlsxWorkbook(SXSSFWorkbook workbook, String pageTitle, List<String> titles, int offRow, List<Object[]> datas, SXSSFSheet sheet) {
        double sheetNo = Math.ceil((double) datas.size() / (double) mMaxSheetRecords);
        for (int index = 0; (double) index <= (sheetNo == 0.0D ? sheetNo : sheetNo - 1.0D); ++index) {
            generateXlsxHeader(workbook, pageTitle, titles, sheet);
            if (null != datas && datas.size() > 0) {
                int startNo = index * mMaxSheetRecords;
                int endNo = Math.min(startNo + mMaxSheetRecords, datas.size());
                int columns = datas.get(0).length;
                if (titles != null) {
                    columns = titles.size();
                }
                for (int i = startNo; i < endNo; ++i) {
                    int rowIndex = i + offRow - startNo;
                    //创建行
                    SXSSFRow bodyRow = sheet.getRow(rowIndex);
                    if (bodyRow == null) {
                        bodyRow = POIUtil.newSXSSFRow(sheet, rowIndex);
                    }
                    for (int j = 0; j < columns; ++j) {
                        //创建单元格
                        SXSSFCell cell = bodyRow.getCell(j);
                        if (cell == null) {
                            cell = POIUtil.newSXSSFCell(bodyRow, j);
                        }
                        buildCellValueByExcelProperty(workbook, cell, datas.get(i)[j]);
                    }
                }
                //自动调整列宽度
                //sheet.trackAllColumnsForAutoSizing();

            }
        }

        return workbook;
    }

    /**
     * 生成EXCEL
     *
     * @param pageTitle 标题名称
     * @param titles    列名称
     * @param datas     数据
     * @return
     */
    private static SXSSFWorkbook generateXlsxWorkbook(SXSSFWorkbook workbook, String pageTitle, List<String> titles, int offRow, List<Object[]> datas) {
        double sheetNo = Math.ceil((double) datas.size() / (double) mMaxSheetRecords);
        for (int index = 0; (double) index <= (sheetNo == 0.0D ? sheetNo : sheetNo - 1.0D); ++index) {
            SXSSFSheet sheet = workbook.getSheetAt(index);
//            if (sheet == null) {
            String sheetName = "sheet_" + (index + 1);//sheet名
            sheet = generateXlsxHeader(workbook, pageTitle, titles, sheetName);
//            }

            if (null != datas && datas.size() > 0) {
                int startNo = index * mMaxSheetRecords;
                int endNo = Math.min(startNo + mMaxSheetRecords, datas.size());
                int columns = datas.get(0).length;
                if (titles != null) {
                    columns = titles.size();
                }
                for (int i = startNo; i < endNo; ++i) {
                    int rowIndex = i + offRow - startNo;
                    //创建行
                    SXSSFRow bodyRow = sheet.getRow(rowIndex);
                    if (bodyRow == null) {
                        bodyRow = POIUtil.newSXSSFRow(sheet, rowIndex);
                    }
                    for (int j = 0; j < columns; ++j) {
                        //创建单元格
                        SXSSFCell cell = bodyRow.getCell(j);
                        if (cell == null) {
                            cell = POIUtil.newSXSSFCell(bodyRow, j);
                        }
                        buildCellValueByExcelProperty(workbook, cell, datas.get(i)[j]);
                    }
                }
                //自动调整列宽度
                //sheet.trackAllColumnsForAutoSizing();

            }
        }

        return workbook;
    }

    /**
     * 生成表格标题和表格列头
     *
     * @param workbook  Excel
     * @param title     标题名称
     * @param columns   列名称
     * @param sheetName sheet名称
     * @return
     */
    private static SXSSFSheet generateXlsxHeader(SXSSFWorkbook workbook, String title, List<String> columns, String sheetName) {
        SXSSFDrawing sxssfDrawing = null;
        SXSSFSheet sheet = POIUtil.newSXSSFSheet(workbook, sheetName);
        SXSSFRow titleRow = POIUtil.newSXSSFRow(sheet, 0);
        for (int i = 0; i < columns.size(); i++) {
            SXSSFCell titleCell = POIUtil.newSXSSFCell(titleRow, i);
            //sheet.setColumnWidth(i, (int)(200 * 35.7));//列宽
            if (i == 0) {
                titleCell.setCellValue(title);
                titleCell.setCellStyle(getPageHeaderCellStyle(workbook));
            }
        }
        //合并单元格
        mergeCell(sheet, 0, 0, 0, columns.size() - 1);

        SXSSFRow headerRow = POIUtil.newSXSSFRow(sheet, 1);
        for (int i = 0; i < columns.size(); i++) {
            String column = columns.get(i);
            SXSSFCell cell = POIUtil.newSXSSFCell(headerRow, i);
            //POIUtil.setColumnWidth(sheet, i, property.getWidth(), property.getColumn());
            cell.setCellStyle(getHeaderCellStyle(workbook));
            cell.setCellValue(column);
        }
        return sheet;
    }

    private static SXSSFSheet generateXlsxHeader(SXSSFWorkbook workbook, String title, List<String> columns, SXSSFSheet sheet) {
        SXSSFDrawing sxssfDrawing = null;
        SXSSFRow titleRow = POIUtil.newSXSSFRow(sheet, 0);
        for (int i = 0; i < columns.size(); i++) {
            SXSSFCell titleCell = POIUtil.newSXSSFCell(titleRow, i);
            //sheet.setColumnWidth(i, (int)(200 * 35.7));//列宽
            if (i == 0) {
                titleCell.setCellValue(title);
                titleCell.setCellStyle(getPageHeaderCellStyle(workbook));
            }
        }
        //合并单元格
        mergeCell(sheet, 0, 0, 0, columns.size() - 1);

        SXSSFRow headerRow = POIUtil.newSXSSFRow(sheet, 1);
        for (int i = 0; i < columns.size(); i++) {
            String column = columns.get(i);
            SXSSFCell cell = POIUtil.newSXSSFCell(headerRow, i);
            //POIUtil.setColumnWidth(sheet, i, property.getWidth(), property.getColumn());
            cell.setCellStyle(getHeaderCellStyle(workbook));
            cell.setCellValue(column);
        }
        return sheet;
    }

    /**
     * 格式化单元格的值（日期格式化居中，数字居右）
     *
     * @param workbook  Excel
     * @param cell      单元格
     * @param cellValue 单元格值
     */
    public static void buildCellValueByExcelProperty(Workbook workbook, Cell cell, Object cellValue) {
        if (null != cellValue) {
            if (cellValue instanceof Date) {
                cellValue = DateFormatUtils.format((Date) cellValue, "yyyy-MM-dd HH:mm:ss");
                cell.setCellStyle(getCenterCellStyle(workbook));
            } else if (cellValue instanceof Number) {
                cell.setCellStyle(getRightCellStyle(workbook));
            }
            cell.setCellValue(String.valueOf(cellValue));
        }
    }

    /**
     * 合并单元格
     *
     * @param sheet     Sheet
     * @param firstRow  开始行，从0开始
     * @param endRow    结束行，从0开始
     * @param firstCell 开始单元格，从0开始
     * @param endCell   结束单元格，从0开始
     */
    public static void mergeCell(SXSSFSheet sheet, int firstRow, int endRow, int firstCell, int endCell) {
        CellRangeAddress regions = new CellRangeAddress(firstRow, endRow, firstCell, endCell);
        sheet.addMergedRegionUnsafe(regions);
    }

    /**
     * 返回表格标题格式
     *
     * @param wb
     * @return
     */
    public static CellStyle getPageHeaderCellStyle(SXSSFWorkbook wb) {
        //if (mPageHeaderCellStyle == null) {
        mPageHeaderCellStyle = wb.createCellStyle();
        mPageHeaderCellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) 16);//字体高度
        mPageHeaderCellStyle.setFont(font);
        //}
        return mPageHeaderCellStyle;
    }

    /**
     * 返回居中样式
     *
     * @param wb
     * @return
     */
    public static CellStyle getCenterCellStyle(Workbook wb) {
        //if (mCenterCellStyle == null) {
        mCenterCellStyle = wb.createCellStyle();
        mCenterCellStyle.setAlignment(HorizontalAlignment.CENTER);
        //}
        return mCenterCellStyle;
    }

    /**
     * 返回居右样式
     *
     * @param wb
     * @return
     */
    public static CellStyle getRightCellStyle(Workbook wb) {
        //if (mRightCellStyle == null) {
        mRightCellStyle = wb.createCellStyle();
        mRightCellStyle.setAlignment(HorizontalAlignment.RIGHT);
        //}
        return mRightCellStyle;
    }

    /**
     * 返回列样式
     *
     * @param wb
     * @return
     */
    public static CellStyle getHeaderCellStyle(SXSSFWorkbook wb) {
        //if (null == mHeaderCellStyle) {
        mHeaderCellStyle = wb.createCellStyle();
        Font font = wb.createFont();
        //mHeaderCellStyle.setFillForegroundColor((short) 12);
        mHeaderCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //mHeaderCellStyle.setBorderTop(BorderStyle.DOTTED);
        //mHeaderCellStyle.setBorderRight(BorderStyle.DOTTED);
        mHeaderCellStyle.setBorderBottom(BorderStyle.DOTTED);
        //mHeaderCellStyle.setBorderLeft(BorderStyle.DOTTED);
        mHeaderCellStyle.setAlignment(HorizontalAlignment.CENTER);// 居中
        //mHeaderCellStyle.setAlignment(HorizontalAlignment.LEFT);// 对齐
        mHeaderCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SEA_GREEN.getIndex());//背景色
        //mHeaderCellStyle.setFillBackgroundColor(HSSFColor.SEA_GREEN.index);
        font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
        // 应用标题字体到标题样式
        mHeaderCellStyle.setFont(font);

        //设置单元格文本形式
        DataFormat dataFormat = wb.createDataFormat();
//            mHeaderCellStyle.setDataFormat(dataFormat.getFormat("#,#0"));
//            mHeaderCellStyle.setDataFormat(dataFormat.getFormat("@"));
        //}
        return mHeaderCellStyle;
    }

    private final static int colsizeN = 630;
    private final static int colsizeM = 1000;

    /**
     * 根据实体类获取注解@ExcelField内容
     * @return excelMapping
     * @Author yjc
     * @Date 2021/5/8
     */

    private static ExcelMapping loadExcelMappingByAnnotation(Class<?> clazz) throws IllegalAccessException, InstantiationException {
            ExcelMapping excelMapping = new ExcelMapping();
            Field[] fields = clazz.getDeclaredFields();
            List<ExcelProperty> propertyList = Lists.newArrayList();
            Field[] arr$ = fields;
            int len$ = fields.length;

            for(int i$ = 0; i$ < len$; ++i$) {
                Field field = arr$[i$];
                ExcelField excelField = field.getAnnotation(ExcelField.class);
                if (null != excelField) {
                    Class<?> emptyClazz = ExcelField.Void.class;
                    ExcelProperty excelMappingProperty = ExcelProperty.builder().name(ValidatorUtil.isEmpty(excelField.name()) ? field.getName() : excelField.name()).required(excelField.required()).column(ValidatorUtil.isEmpty(excelField.value()) ? field.getName() : excelField.value()).comment(excelField.comment()).maxLength(excelField.maxLength()).width(excelField.width()).dateFormat(excelField.dateFormat()).options(excelField.options() != emptyClazz ? excelField.options().newInstance() : null).writeConverterExp(excelField.writeConverterExp()).writeConverter(excelField.writeConverter() != emptyClazz ? excelField.writeConverter().newInstance() : null).readConverterExp(excelField.readConverterExp()).readConverter(excelField.readConverter() != emptyClazz ? excelField.readConverter().newInstance() : null).regularExp(excelField.regularExp()).regularExpMessage(excelField.regularExpMessage()).validator(excelField.validator() != emptyClazz ? excelField.validator().newInstance() : null).build();
                    propertyList.add(excelMappingProperty);
                }
            }

            if (propertyList.isEmpty()) {
                throw new ExcelKitAnnotationAnalyzeException("[" + clazz.getName() + "] @ExcelField annotations not found.");
            } else {
                excelMapping.setPropertyList(propertyList);
                return excelMapping;
            }
    }



    public interface ExcelDataWarp {

        void warpData(int rowIndex, List<String> line) throws ParseException, IllegalAccessException;

        void onError(int sheetIndex, int rowIndex, String message);
    }

//    public interface ExcelDataWarp {
//
//        void warpData(List<String> line) throws Exception;
//
//        void onError(int sheetIndex, int rowIndex, String message);
//    }




}
